﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace WindowsFormsApp1
{
    class DbHelper
    {
        private readonly static string conString = "server=DESKTOP-RQLSIQ5\\SQLEXPRESS;database=Blogs;uid=sa;pwd=20010719;";

        public static SqlConnection conn;

        public static void OpenConn()
        {
            string SqlCon = conString;
            conn = new SqlConnection(SqlCon);
            if (conn.State.ToString().ToLower() == "open")
            {
            }
            else
            {
                conn.Open();
            }
        }

        public static void CloseConn()
        {
            if (conn.State.ToString().ToLower() == "open")
            {
                conn.Close();
                conn.Dispose();
            }
        }

        public static SqlDataReader GetDataReaderValue(string sql)
        {
            OpenConn();
            SqlCommand cmd = new SqlCommand(sql, conn);
            SqlDataReader dr = cmd.ExecuteReader();
            CloseConn();
            return dr;
        }


        public static int AddOrEditDeleteSave(string sql)
        {
            SqlConnection sqlConnection = new SqlConnection(conString);

            SqlCommand command = new SqlCommand(sql, sqlConnection);
            sqlConnection.Open();
            return command.ExecuteNonQuery();
        }

        public DataSet GetDataSetValue(string sql, string tableName)
        {
            OpenConn();
            SqlDataAdapter da = new SqlDataAdapter(sql, conn);
            DataSet ds = new DataSet();
            da.Fill(ds, tableName);
            CloseConn();
            return ds;
        }

        public DataView GetDataViewValue(string sql)
        {
            OpenConn();
            SqlDataAdapter da = new SqlDataAdapter(sql, conn);
            DataSet ds = new DataSet();
            da.Fill(ds, "temp");
            CloseConn();
            return ds.Tables[0].DefaultView;
        }

        public static DataTable GetDataTable(string sql)
        {
            SqlConnection sqlConnection = new SqlConnection(conString);
            SqlDataAdapter dataAdapter = new SqlDataAdapter(sql, sqlConnection);
            DataTable dt = new DataTable();
            sqlConnection.Open();
            dataAdapter.Fill(dt);
            return dt;
        }

        public DataTable GetDataTableValue(string sql)
        {
            OpenConn();
            DataTable dt = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sql, conn);
            da.Fill(dt);
            CloseConn();
            return dt;
        }

        public void ExecuteNonQuery(string sql)
        {
            OpenConn();
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.ExecuteNonQuery();
            cmd.Dispose();
            CloseConn();
        }


        public int ExecuteNonQueryCount(string sql)
        {
            OpenConn();
            SqlCommand cmd = new SqlCommand(sql, conn);
            int value = cmd.ExecuteNonQuery();
            return value;
        }

        public object ExecuteScalar(string sql)
        {
            OpenConn();
            SqlCommand cmd = new SqlCommand(sql, conn);
            object value = cmd.ExecuteScalar();
            return value;
        }

        public int SqlServerRecordCount(string sql)
        {
            OpenConn();
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = sql;
            cmd.Connection = conn;
            SqlDataReader dr = cmd.ExecuteReader();
            int RecordCount = 0;
            while (dr.Read())
            {
                RecordCount = RecordCount + 1;
            }
            CloseConn();
            return RecordCount;
        }

    }

}
